package com.pengshun.modules.api.mapper;

import com.pengshun.modules.api.coal.service.dto.UserDto;
import com.pengshun.modules.api.news.service.dto.CategoryDto;
import com.pengshun.modules.api.system.domain.CityDto;
import com.pengshun.modules.api.system.domain.Citys;
import com.pengshun.modules.api.system.domain.District;
import com.pengshun.modules.api.system.domain.Province;
import com.pengshun.modules.api.system.service.dto.AddresDto;
import com.pengshun.modules.api.system.service.dto.Content;
import com.pengshun.modules.domain.*;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

public interface SystemMapper {

    /**
     * 查询所有省份
     * @return
     */
    @Select("select pp.province_id provinceId  ,pp.province from  ps_province pp")
    List<Province> selectAllProvince();

    /**
     * 根据id查询城市
     * @param provinceId
     * @return
     */
    @Select("select pp.province_id provinceId  ,pp.city,pp.city_id cityId, pp.first_letter firstLetter from  ps_city pp where pp.province_id=#{provinceId}")
    List<CityDto> selectCityBy(@Param("provinceId") Integer provinceId);

    /**
     * 根据id查询区县
     * @param cityId
     * @return
     */
    @Select("SELECT pd.`city_id` cityId,pd.`district`,pd.`district_id` districtId  FROM  `ps_district`  pd  WHERE pd.`city_id`=#{cityId}")
    List<District> selectDstrictBy(@Param("cityId")Integer cityId);

    /**
     * 查询轮播图
     * @param type
     * @return
     */
    @Select("select image ,urls  from  ps_activity where type=#{type} and status=1 order by crate_time")
    List<Activity> selectActivity(@Param("type")Integer type);

    /**
     * 根据区id查询省市id
     * @param adcode
     * @return
     */
    @Select("SELECT  pc.`city_id` cityId,pc.`province_id` provinceId ,pc.`city`,pd.`district`,pp.`province` " +
            "FROM  ps_city pc  " +
            "INNER JOIN ps_district pd ON pd.`city_id`=pc.`city_id`  " +
            "INNER JOIN ps_province pp ON pp.`province_id`=pc.`province_id` " +
            "WHERE pd.`district_id`=#{adcode}")
    AddresDto selectBysdId(@Param("adcode")Integer adcode);

    @Select("SELECT  id,version_code versionCode,version_name versionName,version_url versionUrl,is_mandatory isMandatory, remark, creat_time creatTime FROM ps_app_version  order by creat_time desc limit 0,1 ")
    AppVersion selectappVersion();

    /**
     * 操作表添加记录
     * @param content
     * @param id
     * @param type
     * @param userId
     * @return
     */
    @Insert("INSERT INTO `ps_operate_record` (`content`, `id`, `type`,  `user_id`)  " +
            "VALUES " +
            "  (#{content},#{id},#{type},#{userId})")
    int insertOperateRecord(@Param("content") String content,@Param("id") Long id,@Param("type") Long type,@Param("userId") Long userId);

    /**
     * 根据编码查询类别
     * @return
     */
    @Select("SELECT  a.`category_id` id ,a.`category_name` as `name` ,a.`category_id`as `value`  FROM `ps_category`  a  " +
            " WHERE a.`parent_id`=(SELECT category_id FROM ps_category WHERE `code`=#{name}) and a.`status`=1 order by `sort`")
    List<Content> selectBaseCategory(@Param("name") String name);

    /**
     * 查询所有煤矿
     * @return
     */
    @Select("SELECT  p.`mine_mouth_id` id ,p.`mine_mouth_name` `name` ,p.`mine_mouth_id` `value` FROM  ps_mine_mouth  p")
    List<Content> selectMineMouth();

    /**
     * 查询所有煤种
     * @return
     */
    @Select("SELECT  p.`coal_category_id` id, p.`name`,p.`coal_category_id` `value` FROM   `ps_coal_category`  p WHERE p.`enabled`=1")
    List<Content> selectCoalCategory();


    /**
     * 查询所有车辆长度
     * @return
     */
    @Select("select  p.`vehicle_length_id` id,p.`name`, p.`vehicle_length_id` `value`from   `ps_vehicle_length`   p where p.`status`=1 order by p.`sort`")
    List<Content> selectvehicleLength();

    /**
     * 查询所有车辆类型
     * @return
     */
    @Select("SELECT p.`vehicle_type_id` id,p.`name`,p.`vehicle_type_id` `value` FROM   `ps_vehicle_type` p WHERE p.`status`=1 ORDER BY p.`sort`")
    List<Content> selectvehicleType();

    @Select("SELECT pd.`city_id` cityId,pd.`district`,pd.`district_id` districtId  FROM  `ps_district`  pd  WHERE pd.`district_id`=#{shipDistrictAdcode}")
    District selectDstrictDetil(@Param("shipDistrictAdcode") String shipDistrictAdcode);

    /**
     * 删除身份认证信息
     * @param id
     */
    @Delete("DELETE FROM `ps_citizen` WHERE  `user_id`=#{id} ")
    int deleteCitizenByUserId(@Param("id") Long id);

    /**
     * 删除司机表信息
     * @param id
     */
    @Delete("DELETE FROM `ps_driving_license` WHERE  `user_id`=#{id} ")
    int deleteDrivingLicenseByUserId(@Param("id") Long id);

    /**
     * 删除商户表信息
     * @param id
     */
    @Delete("DELETE FROM `ps_merchant` WHERE  `user_id`=#{id} ")
    int deleteMerchantByUserId(@Param("id") Long id);

    /**
     * 用户角色表添加数据
     * @param roleId
     * @param userId
     * @return
     */
    @Insert("INSERT  INTO sys_users_roles (`user_id`, `role_id`) values(#{userId},#{roleId})")
    int insertUserRole(@Param("roleId") Long roleId, @Param("userId") Long userId);

    /**
     * 删除角色表数据
     * @param id
     * @return
     */
    @Delete("DELETE FROM  `sys_users_roles` WHERE `user_id` =#{id}")
    int deleteUserRole(@Param("id") Long id);

    /**
     * 查询所有城市
     * @return
     */
    @Select("SELECT  `city_id` cityId,`city`,`province_id` provinceId,`first_letter` firstLetter  FROM  ps_city   ORDER BY first_letter,second_letter")
    List<CityDto> findAllCityOrderByFirstLetterSecondLetter();

    /**
     * 城市模糊查询
     * @param city
     * @return
     */
    @Select("SELECT  `city_id` cityId,`city`,`province_id` provinceId,`first_letter` firstLetter  FROM  ps_city  where  city like  CONCAT('%',#{city},'%')   ORDER BY first_letter,second_letter")
    List<CityDto> selectByCity(@Param("city") String city);

    /**
     * 联系我们查询
     * @return
     */
    @Select("select * from ps_contact_ours")
    PsContactOurs findContactOurs();

    /**
     *  意见反馈保存
     * @param content
     * @param photos
     * @param userId
     * @param date
     * @return
     */
    @Insert("INSERT INTO `ps_suggest` (`content`,`photos`,`user_id`,`create_time`) VALUES (" +
            " #{content},#{photos},#{userId},#{date}  )")
    int saveSuggest(@Param("content") String content, @Param("photos") String photos,  @Param("userId") Long userId, @Param("date") Date date);

    /**
     * 查询公司简介
     * @return
     */
    @Select("select * from ps_company_introduction")
    PsSuggest findIntroduction();

    /**
     * 查询系统公告
     * @return
     */
    @Select("select * from ps_system_notice order by create_time desc limit 0,1")
    SystemNotice selectNotice();

    /**
     * 查询系统配置状态
     * @param type
     * @return
     */
    @Select("select status from ps_system_set where type=#{type}")
    int findSystemSetStatus(@Param("type") int type);

    /**
     * 查询系统银行卡
     * @return
     */
    @Select("select * from  ps_system_bank where status=1 order by system_bank_id")
    List<SystemBank> systemBanKList();

    /**
     * 根据卡号查询银行
     * @param code
     * @return
     */
    @Select("select * from   ps_system_bank where status=1 and code  =#{code}")
    SystemBank findSystemBankByCode(@Param("code") String code);

    /**
     * 查询客服列表
     * @return
     */
    @Select("SELECT  su.`user_id`,su.`avatar_name`,su.`nick_name` FROM ps_customer_service m INNER JOIN  sys_user su ON m.`user_id`=su.`user_id` ORDER BY RAND() LIMIT 1")
    List<UserDto> selectCustomerList();

    @Select("SELECT  su.`user_id`,su.`avatar_name`,su.`nick_name` FROM ps_merchant m INNER JOIN  sys_user su ON m.`user_id`=su.`user_id` WHERE su.`status`=2")
    List<UserDto> selectMerchantList();
    @Select("SELECT  su.`user_id`,su.`avatar_name`,su.`nick_name` FROM ps_customer_service m INNER JOIN  sys_user su ON m.`user_id`=su.`user_id`")
    List<UserDto> selectAllCustomerList();
}
